import 'package:dartkit/dartkit.dart';
import 'package:postgres/postgres.dart';

part 'model.dart';

class DiaryTable extends TableForUser<Diary> {
  @override
  final Connection conn;
  @override
  final String tableName = "diaries";
  @override
  Diary unzip(Json raw) => Diary.fromRaw(raw);

  DiaryTable(this.conn);

  @override
  Future<void> createTable() async {
    await conn.execute("""
      CREATE TABLE IF NOT EXISTS $tableName (
          id SERIAL PRIMARY KEY,
          user_id INT NOT NULL,
          date DATE NOT NULL,
          title VARCHAR(50) NOT NULL DEFAULT '',
          content TEXT NOT NULL DEFAULT '',
          UNIQUE (user_id, date),
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );
    """);

    // 创建 date 列的索引
    await conn.execute("""
      CREATE INDEX IF NOT EXISTS idx_${tableName}_date ON $tableName (date);
    """);

    // 创建 user_id 列的索引
    await conn.execute("""
  CREATE INDEX IF NOT EXISTS idx_${tableName}_user_id ON $tableName (user_id);
""");
  }

  @override
  Future<Diary> create(Json args) async {
    // 读取id和日期
    final Date date = args.getField<Date>("date", "创建日记需要传入日期(date)");
    final int userId = args.getField<int>("user_id", "创建日记需要传入用户id");

    // 插入新的日记记录
    final result = await conn.execute(Sql.named("""
        INSERT INTO $tableName (user_id, date, title, content)
        VALUES (@user_id, @date, DEFAULT, DEFAULT)
        RETURNING *;
      """), parameters: {
      'user_id': userId,
      'date': date.toString(),
    });
    if (result.isEmpty) throw BugDev("创建日记成功，但找不到日记记录");

    // 获取插入后的第一行数据
    final row = result.first;
    return Diary.fromRaw(row.toColumnMap());
  }

  @override
  Future<void> update(Diary item) async {
    // 执行更新操作
    final result = await conn.execute(Sql.named("""
    UPDATE $tableName
    SET title = @title, content = @content
    WHERE id = @id;
  """), parameters: {
      "id": item.id,
      'title': item.title,
      'content': item.content,
    });

    if (result.affectedRows == 0) throw BugDev("根据日记id[${item.id}]找不到番茄来更新");
  }
}

final DiaryTable diaryTable = DiaryTable(argConn.value);
